CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_ROBOT6" is

  -- 根据当日持有etf的收盘价，在卖etf之前，更新robot6_account表
  -- 游标名称update_robot_account_before_sell_or_buy
  procedure update_robot_account_b_s_b(p_date in varchar2) as
  begin
    declare
      -- etf资产
      v_etf_assets number;
      -- 账户名称
      v_robot_name varchar2(20);
      -- 某只etf的当前收盘价
      v_current_close_price number;
      -- robot6_account表的所有记录
      cursor cur_robot_account is
        select * from robot6_account;
      -- 某个账户所有没有卖出的etf。用于做多
      -- 游标名称：cur_bull_robot6_etf_transact_record
      cursor cur_bull_r_e_t_r is
        select *
          from robot6_stock_transact_record t
         where t.robot_name = v_robot_name
           and t.sell_date is null
           and t.sell_price is null
           and t.sell_amount is null
           and t.direction = 1;
    begin
      for row_robot_account in cur_robot_account loop
        v_robot_name          := row_robot_account.robot_name;
        v_etf_assets          := 0;
        v_current_close_price := null;
      
        -- 做多
        -- 计算这个账户的所有etf的etf资产
        for row_robot_etf_transac_record in cur_bull_r_e_t_r loop
          -- 需要重置为null，否则会留有一个etf的价格
          v_current_close_price := null;
          -- 查找某只etf在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from etf_transaction_data t
             where t.code_ = row_robot_etf_transac_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明etf在这一天没有交易记录
              DBMS_OUTPUT.put_line('etf【' ||
                                   row_robot_etf_transac_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from etf_transaction_data t
                     where t.code_ = row_robot_etf_transac_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 计算这只etf股票资产
          v_etf_assets := v_etf_assets +
                          v_current_close_price *
                          row_robot_etf_transac_record.buy_amount;
        
        end loop;
      
        -- 更新robot6_account表的etf_assets、total_assets字段
        if v_etf_assets is not null and v_etf_assets != 0 then
          update robot6_account t
             set t.stock_assets   = v_etf_assets,
                 t.total_assets = t.capital_assets + v_etf_assets
           where t.robot_name = row_robot_account.robot_name;
          commit;
        end if;
      end loop;
    end;
  end update_robot_account_b_s_b;

  --------------------------------------------------------------------------------------------------

  -- 卖etf，只修改robot6_stock_transact_record表中的记录
  /* p_mandatory_stop_loss等于1，表示有强制止损；p_mandatory_stop_loss等于0，表示没有强制止损 */
  procedure sell_or_buy(p_date                     in varchar2,
                        p_mandatory_stop_loss      in number,
                        p_mandatory_stop_loss_rate in number,
                        p_mdl_etf_type             in number) as
  begin
    declare
      -- 机器人账户名称
      v_robot_name varchar2(20);
      -- 卖出/买入价格
      v_sell_or_buy_price number;
      -- 是否卖出/买入。1表示准备卖出/买入，0表示不准备卖出/买入
      v_do_sell_buy number := 0;
      -- 四种算法是否是交易日的变量
      v_mdl_etf_macd_gold_cross number;
      v_mdl_etf_c_p_ma5_g_c     number;
      v_mdl_etf_h_k_a_down_up   number;
      v_mdl_etf_kd_gold_cross   number;
      -- robot_account表的所有记录
      cursor cur_robot_account is
        select * from robot6_account;
      -- 某个机器人账户的买入的etf的交易记录
      -- 游标名称 cur_bull_robot_etf_transaction_record
      cursor cur_bull_r_e_t_r is
        select *
          from robot6_stock_transact_record t
         where t.robot_name = v_robot_name
           and t.SELL_DATE is null
           and t.SELL_PRICE is null
           and t.SELL_AMOUNT is null
           and t.direction = 1;
    begin
      for row_robot_account in cur_robot_account loop
      
        -- 如果这个账户没有持有etf，则查找下一个账户
        if row_robot_account.hold_stock_number = 0 then
          continue;
        end if;
      
        v_robot_name := row_robot_account.robot_name;
      
        -- 做多
        for row_bull_r_e_t_r in cur_bull_r_e_t_r loop
        
          -- 判断是否有强制止损
          if p_mandatory_stop_loss = 1 then
            begin
              select t1.close_price
                into v_sell_or_buy_price
                from etf_transaction_data t1
               where t1.code_ = row_bull_r_e_t_r.stock_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd');
              if (v_sell_or_buy_price - row_bull_r_e_t_r.buy_price) /
                 row_bull_r_e_t_r.buy_price * 100 <=
                 -p_mandatory_stop_loss_rate then
                v_do_sell_buy := 1;
              end if;
            exception
              when no_data_found then
                -- 说明etf在这一天没有交易记录
                DBMS_OUTPUT.put_line('etf【' || row_bull_r_e_t_r.stock_code ||
                                     '】在日期【' || p_date || '】没有交易记录');
            end;
          end if;
        
          -- 判断是否是死叉，如果不是死叉，则返回下一条记录
          -- MACD死叉
          if row_bull_r_e_t_r.filter_type = 1 then
            select count(*)
              into v_mdl_etf_macd_gold_cross
              from mdl_etf_macd_gold_cross t
             where t.etf_code = row_bull_r_e_t_r.stock_code
               and t.sell_date = to_date(p_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
            if v_mdl_etf_macd_gold_cross >= 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 收盘价死叉五日均线
          if row_bull_r_e_t_r.filter_type = 3 then
            select count(*)
              into v_mdl_etf_c_p_ma5_g_c
              from mdl_etf_close_price_ma5_g_c t
             where t.etf_code = row_bull_r_e_t_r.stock_code
               and t.sell_date = to_date(p_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
            if v_mdl_etf_c_p_ma5_g_c >= 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 平均K线是否从上涨趋势变为下跌趋势
          if row_bull_r_e_t_r.filter_type = 5 then
            select count(*)
              into v_mdl_etf_h_k_a_down_up
              from mdl_etf_hei_kin_ashi_down_up t
             where t.etf_code = row_bull_r_e_t_r.stock_code
               and t.sell_date = to_date(p_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
            if v_mdl_etf_h_k_a_down_up >= 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- KD死叉
          if row_bull_r_e_t_r.filter_type = 7 then
            select count(*)
              into v_mdl_etf_kd_gold_cross
              from mdl_etf_kd_gold_cross t
             where t.etf_code = row_bull_r_e_t_r.stock_code
               and t.sell_date = to_date(p_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
            if v_mdl_etf_kd_gold_cross >= 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          -- 如果前一交易日收盘价大于布林带下轨，并且当前交易日收盘价小于前一交易日收盘价，则反弹结束
          if row_bull_r_e_t_r.filter_type = 9 then
            if SCOTT.fnc_judge_cp_up_end_f_b_boll(p_date,
                                                  row_bull_r_e_t_r.stock_code) = 1 then
              v_do_sell_buy := 1;
            end if;
          end if;
        
          if v_do_sell_buy = 1 then
            -- 如果是死叉，则卖出这支etf
            -- 查找日期为p_date的某只etf的收盘价（卖出价格）
            begin
              select t1.close_price
                into v_sell_or_buy_price
                from etf_transaction_data t1
               where t1.code_ = row_bull_r_e_t_r.stock_code
                 and t1.date_ = to_date(p_date, 'yyyy-mm-dd');
            exception
              when no_data_found then
                -- 重置
                -- v_do_sell_buy := 0;
                -- 说明etf在这一天没有交易记录
                DBMS_OUTPUT.put_line('etf【' || row_bull_r_e_t_r.stock_code ||
                                     '】在日期【' || p_date || '】没有交易记录');
                continue;
            end;
          
            -- 更新robot6_etf_transact_record表的sell_date、sell_price和sell_amount字段
            update robot6_stock_transact_record t
               set t.sell_date   = to_date(p_date, 'yyyy-mm-dd'),
                   t.sell_price  = v_sell_or_buy_price,
                   t.sell_amount = t.buy_amount
             where t.id_ = row_bull_r_e_t_r.id_;
            commit;
            -- 更新robot6_stock_transact_record表的profit_and_loss和profit_and_loss_rate字段
            update robot6_stock_transact_record t
               set t.profit_and_loss     =
                   (t.sell_price * t.sell_amount) -
                   (t.buy_price * t.buy_amount),
                   t.profit_and_loss_rate =
                   (t.sell_price - t.buy_price) / t.buy_price * 100
             where t.id_ = row_bull_r_e_t_r.id_;
            commit;
          end if;
        
          -- 重置
          v_do_sell_buy := 0;
        end loop;
      end loop;
    end;
  end sell_or_buy;

  --------------------------------------------------------------------------------------------------

  -- 根据当日卖出etf的收盘价，在卖etf之后，更新robot6_account表
  procedure update_robot_account_after_s_b(p_date in varchar2) as
  begin
    declare
      -- 机器人6账号
      v_robot_name varchar2(20);
      -- etf资产
      v_etf_assets number;
      -- 卖出etf的数量
      v_sell_or_buy_etf_number number;
      -- etf的收盘价
      v_current_close_price number;
      -- 表robot_etf_transaction_record类型的记录
      row_robot_etf_tranc_record robot6_stock_transact_record%rowtype;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot6_account;
      -- 计算每一个机器人账户，在某一天卖完etf后的收益
      cursor cur_bull_etf is
        select *
          from robot6_stock_transact_record retr
         where retr.robot_name = v_robot_name
           and retr.direction = 1
           and retr.sell_date is null
           and retr.sell_price is null
           and retr.sell_amount is null;
    begin
      ----------------------------------- 做多 ---------------------------------
      for row_robot_account in cur_robot_account loop
        -- 机器人账户名称
        v_robot_name             := row_robot_account.robot_name;
        v_etf_assets             := 0;
        v_sell_or_buy_etf_number := 0;
        v_current_close_price    := null;
      
        -- 做多
        for row_robot_etf_tranc_record in cur_bull_etf loop
        
          -- 查找某只etf在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from etf_transaction_data t
             where t.code_ = row_robot_etf_tranc_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明etf在这一天没有交易记录
              DBMS_OUTPUT.put_line('etf【' ||
                                   row_robot_etf_tranc_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from etf_transaction_data t
                     where t.code_ = row_robot_etf_tranc_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 卖完股票后的收益
          v_etf_assets             := v_etf_assets +
                                      v_current_close_price *
                                      row_robot_etf_tranc_record.buy_amount;
          v_sell_or_buy_etf_number := v_sell_or_buy_etf_number + 1;
        end loop;
      
        -- 更新robot6_account表
        update robot6_account t
           set t.hold_stock_number = v_sell_or_buy_etf_number,
               t.stock_assets      = v_etf_assets,
               t.capital_assets  = t.total_assets - v_etf_assets
         where t.robot_name = v_robot_name;
        commit;
      end loop;
    end;
  end update_robot_account_after_s_b;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：删除除过权的etf
  procedure filter_by_xr(p_begin_date in varchar2, p_end_date in varchar2) as
  begin
    declare
      -- etf代码
      v_code varchar2(10);
      -- 查询robot6_stock_filter表的全部记录
      cursor cur_robot_etf_filter is
        select * from robot6_stock_filter;
    begin
      for cur_rsf in cur_robot_etf_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 判断这只etf在指定时间范围内是否出现了除权
        if fnc_etf_xr(v_code, p_begin_date, p_end_date) = 1 then
          delete from robot6_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      end loop;
    end;
  end filter_by_xr;

  --------------------------------------------------------------------------------------------

  -- 按照条件：MA不单调递减，过滤股票
  procedure filter_by_ma_not_decreasing(p_ma_level in number,
                                        p_date     in varchar2,
                                        p_rownum   in number) as
  begin
    declare
      -- ETF代码
      v_code varchar2(10);
      -- 下一日的5日均线
      v_next_ma5 number;
      -- 下一日的10日均线
      v_next_ma10 number;
      -- 下一日的20日均线
      v_next_ma20 number;
      -- 下一日的60日均线
      v_next_ma60 number;
      -- 下一日的120日均线
      v_next_ma120 number;
      -- 下一日的250日均线
      v_next_ma250 number;
      -- 表示是否是单调不递减，-1表示否，1表示是
      v_increasing number := 1;
      -- 查询robot6_stock_filter表的全部记录
      cursor cur_robot6_stock_filter is
        select * from robot6_stock_filter;
      -- 根据条件code、date，查找etf_transaction_data表，按降序排列，只取前rownum条记录
      cursor cur_find_c_a_d_o_d_d_ltq_rn is
        select *
          from (select *
                  from etf_transaction_data t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_rownum;
    begin
      for cur_rsf in cur_robot6_stock_filter loop
        v_code := cur_rsf.stock_code;
      
        -- 重新设置为1
        v_increasing := 1;
      
        for cur_fbcadobddlr in cur_find_c_a_d_o_d_d_ltq_rn loop
        
          -- 5日均线
          if p_ma_level = 5 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma5字段
              select b.ma5
                into v_next_ma5
                from (select *
                        from etf_transaction_data t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma5 is null or v_next_ma5 > cur_fbcadobddlr.ma5 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断5日均线是否不单调递减时，etf' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma5 := cur_fbcadobddlr.ma5;
          end if;
        
          -- 10日均线
          if p_ma_level = 10 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma10字段
              select b.ma10
                into v_next_ma10
                from (select *
                        from etf_transaction_data t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma10 is null or v_next_ma10 > cur_fbcadobddlr.ma10 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断10日均线是否不单调递减时，etf' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma10 := cur_fbcadobddlr.ma10;
          end if;
        
          -- 20日均线
          if p_ma_level = 20 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma20字段
              select b.ma20
                into v_next_ma20
                from (select *
                        from etf_transaction_data t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma20 is null or v_next_ma20 > cur_fbcadobddlr.ma20 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断20日均线是否不单调递减时，etf' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma20 := cur_fbcadobddlr.ma20;
          end if;
        
          -- 60日均线
          if p_ma_level = 60 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma60字段
              select b.ma60
                into v_next_ma60
                from (select *
                        from etf_transaction_data t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma60 is null or v_next_ma60 > cur_fbcadobddlr.ma60 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断60日均线是否不单调递减时，etf' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma60 := cur_fbcadobddlr.ma60;
          end if;
        
          -- 120日均线
          if p_ma_level = 120 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma120字段
              select b.ma120
                into v_next_ma120
                from (select *
                        from etf_transaction_data t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma120 is null or
                 v_next_ma120 > cur_fbcadobddlr.ma120 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断120日均线是否不单调递减时，etf' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma120 := cur_fbcadobddlr.ma120;
          end if;
        
          -- 250日均线
          if p_ma_level = 250 then
            begin
              -- 返回游标cur_find_by_code_and_date_order_by_date_desc_ltq_rownum下一条记录的ma250字段
              select b.ma250
                into v_next_ma250
                from (select *
                        from etf_transaction_data t
                       where t.code_ = v_code
                         and t.date_ < cur_fbcadobddlr.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果出现了递减的情况，则直接跳出这个循环
              if v_next_ma250 is null or
                 v_next_ma250 > cur_fbcadobddlr.ma250 then
                v_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_rownum个，则也认为是不满足不单调递减条件
                v_increasing := -1;
                DBMS_OUTPUT.put_line('判断250日均线是否不单调递减时，etf' || v_code ||
                                     '均线数量不足' || p_rownum ||
                                     '个，则也认为是不满足不单调递减条件');
                exit;
            end;
          
            -- 为了下一步的迭代
            -- v_current_ma250 := cur_fbcadobddlr.ma250;
          end if;
        
        end loop;
      
        -- 如果出现了递减的情况，则将这个etf从robot6_stock_filter表中删除
        if v_increasing = -1 then
          delete from robot6_stock_filter t where t.stock_code = v_code;
          commit;
        end if;
      end loop;
    end;
  end filter_by_ma_not_decreasing;
  --------------------------------------------------------------------------------------------------

  -- 更新robot6_stock_filter表：direction为1；filter_type为交易算法；accumulative_profit_rate累计收益。单位：元
  procedure update_robot_stock_filter(p_date in varchar2) as
  begin
    declare
      -- 最大收益率
      v_max_profit_loss number := -100;
      -- 最大收益率类型
      v_max_profit_loss_type number := 0;
      -- robot6_stock_filter表类型的变量
      row_robot_etf_filter robot6_stock_filter%rowtype;
      -- mdl_etf_macd_gold_cross表类型的变量
      row_mdl_etf_macd_gold_cross mdl_etf_macd_gold_cross%rowtype;
      -- mdl_etf_close_price_ma5_g_c表类型的变量
      row_mdl_etf_c_p_ma5_g_c mdl_etf_close_price_ma5_g_c%rowtype;
      -- mdl_etf_hei_kin_ashi_down_up表类型的变量
      row_mdl_etf_k_a_down_up mdl_etf_hei_kin_ashi_down_up%rowtype;
      -- mdl_etf_kd_gold_cross表类型的变量
      row_mdl_etf_kd_gold_cross mdl_etf_kd_gold_cross%rowtype;
      -- robot6_stock_filter表
      cursor cur_robot_etf_filter is
        select * from robot6_stock_filter;
    begin
      for row_robot_etf_filter in cur_robot_etf_filter loop
        -- 重置
        v_max_profit_loss      := -100;
        v_max_profit_loss_type := 0;
      
        -- 计算最大收益率、最大收益率类型
        select *
          into row_mdl_etf_macd_gold_cross
          from (select *
                  from mdl_etf_macd_gold_cross t
                 where t.etf_code = row_robot_etf_filter.stock_code
                   and t.sell_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.sell_date desc) t1
         where rownum <= 1;
        if row_mdl_etf_macd_gold_cross.accumulative_profit_loss >
           v_max_profit_loss then
          v_max_profit_loss      := row_mdl_etf_macd_gold_cross.accumulative_profit_loss;
          v_max_profit_loss_type := 1;
        end if;
      
        select *
          into row_mdl_etf_c_p_ma5_g_c
          from (select *
                  from mdl_etf_close_price_ma5_g_c t
                 where t.etf_code = row_robot_etf_filter.stock_code
                   and t.sell_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.sell_date desc) t1
         where rownum <= 1;
        if row_mdl_etf_c_p_ma5_g_c.accumulative_profit_loss >
           v_max_profit_loss then
          v_max_profit_loss      := row_mdl_etf_c_p_ma5_g_c.accumulative_profit_loss;
          v_max_profit_loss_type := 3;
        end if;
      
        select *
          into row_mdl_etf_k_a_down_up
          from (select *
                  from mdl_etf_hei_kin_ashi_down_up t
                 where t.etf_code = row_robot_etf_filter.stock_code
                   and t.sell_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.sell_date desc) t1
         where rownum <= 1;
        if row_mdl_etf_k_a_down_up.accumulative_profit_loss >
           v_max_profit_loss then
          v_max_profit_loss      := row_mdl_etf_k_a_down_up.accumulative_profit_loss;
          v_max_profit_loss_type := 5;
        end if;
      
        select *
          into row_mdl_etf_kd_gold_cross
          from (select *
                  from mdl_etf_kd_gold_cross t
                 where t.etf_code = row_robot_etf_filter.stock_code
                   and t.sell_date <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.sell_date desc) t1
         where rownum <= 1;
        if row_mdl_etf_kd_gold_cross.accumulative_profit_loss >
           v_max_profit_loss then
          v_max_profit_loss      := row_mdl_etf_kd_gold_cross.accumulative_profit_loss;
          v_max_profit_loss_type := 7;
        end if;
      
        DBMS_OUTPUT.put_line('最大收益率是：' || v_max_profit_loss ||
                             '，最大收益率类型是：' || v_max_profit_loss_type);
      
        -- 更新
        update robot6_stock_filter t
           set t.direction                = 1,
               t.filter_type              = v_max_profit_loss_type,
               t.accumulative_profit_loss = v_max_profit_loss
         where t.stock_code = row_robot_etf_filter.stock_code;
      end loop;
      commit;
    end;
  end update_robot_stock_filter;

  --------------------------------------------------------------------------------------------------

  -- 过滤条件：从robot6_stock_filter表中删除那些不是交易日的记录
  PROCEDURE filter_by_buy_date(p_buy_date     in varchar2,
                               p_mdl_etf_type in number) as
  begin
    declare
      -- robot6_stock_filter表类型的变量
      row_robot_etf_filter robot6_stock_filter%rowtype;
      -- 四种算法的表格的变量
      row_mdl_etf_macd_gold_cross mdl_etf_macd_gold_cross%rowtype;
      row_mdl_etf_c_p_ma5_g_c     mdl_etf_close_price_ma5_g_c%rowtype;
      row_mdl_etf_h_k_a_down_up   mdl_etf_hei_kin_ashi_down_up%rowtype;
      row_mdl_etf_kd_gold_cross   mdl_etf_kd_gold_cross%rowtype;
      -- 返回机器人账户
      cursor cur_robot_etf_filter is
        select * from robot6_stock_filter;
    begin
      for row_robot_etf_filter in cur_robot_etf_filter loop
        -- 如果当前日期不是交易日，则跳过，并删除robot6_stock_filter中的记录
        begin
          if row_robot_etf_filter.filter_type = 1 then
            select *
              into row_mdl_etf_macd_gold_cross
              from mdl_etf_macd_gold_cross t
             where t.etf_code = row_robot_etf_filter.stock_code
               and t.buy_date = to_date(p_buy_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
          end if;
          if row_robot_etf_filter.filter_type = 3 then
            select *
              into row_mdl_etf_c_p_ma5_g_c
              from mdl_etf_close_price_ma5_g_c t
             where t.etf_code = row_robot_etf_filter.stock_code
               and t.buy_date = to_date(p_buy_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
          end if;
          if row_robot_etf_filter.filter_type = 5 then
            select *
              into row_mdl_etf_h_k_a_down_up
              from mdl_etf_hei_kin_ashi_down_up t
             where t.etf_code = row_robot_etf_filter.stock_code
               and t.buy_date = to_date(p_buy_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
          end if;
          if row_robot_etf_filter.filter_type = 7 then
            select *
              into row_mdl_etf_kd_gold_cross
              from mdl_etf_kd_gold_cross t
             where t.etf_code = row_robot_etf_filter.stock_code
               and t.buy_date = to_date(p_buy_date, 'yyyy-mm-dd')
               and t.type_ = p_mdl_etf_type;
          end if;
        exception
          when no_data_found then
            DBMS_OUTPUT.put_line('etf：' || row_robot_etf_filter.stock_code ||
                                 '在日期：' || p_buy_date || '不是交易日');
            -- 从robot6_stock_filter表中删除这条记录
            delete from robot6_stock_filter t
             where t.stock_code = row_robot_etf_filter.stock_code;
            commit;
            continue;
        end;
      end loop;
    end;
  end filter_by_buy_date;

  --------------------------------------------------------------------------------------------------
  -- 10个股票平分仓位
  -- 买股票，只向robot6_stock_transaction_record表中插入记录，只更新robot6_account表的hold_stock_number字段
  procedure buy_or_sell_avg_hold_space(p_buy_date            in varchar2,
                                       p_max_hold_etf_number in number) as
  begin
    declare
      -- robot6_stock_filter表类型的变量
      row_robot_etf_filter robot6_stock_filter%rowtype;
      -- 收盘价
      v_close_price number;
      -- 买入多少股
      v_buy_or_sell_amount number;
      -- 当前的资金资产
      v_capital_assets number;
      -- 当前的总资产
      v_total_assets number;
      -- 当前持有etf的数量
      v_hold_etf_number number;
      -- 应当买入的etf的数量
      v_should_buy_etf_number number := 0;
      -- robot6_stock_filter表的记录数
      -- v_robot6_etf_filter_number number;
      -- 调用存储过程的返回值
      --v_macd_success_rate_array T_MACD_SUCCESS_RATE_ARRAY;
      -- 调用存储过程的返回值
      --v_c_p_ma5_success_rate_array T_C_P_MA5_SUCCESS_RATE_ARRAY;
      -- 调用存储过程的返回值
      --v_h_k_a_success_rate_arr T_H_K_A_SUCCESS_RATE_ARRAY;
      -- 当前的成功率（有延迟）
      -- v_current_success_rate number;
      -- 最大成功率
      --v_max_success_rate number;
      -- 最小成功率
      --v_min_success_rate number;
      -- 可以有的最大仓位
      --v_max_shipment_space number;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot6_account;
      -- 按照accumulative_profit_loss列降序排列，取第一条记录；如果robot6_stock_filter表中已经没有数据了，则查找下一个机器人
      cursor cur_robot_etf_filter is
        select *
          from (select *
                  from robot6_stock_filter t
                 order by t.accumulative_profit_loss desc)
        -- where rownum = 1;
         where rownum <= p_max_hold_etf_number;
    begin
      for row_robot_account in cur_robot_account loop
        -- 如果当前账户的持股数量已经大于等于最大持股数量，则查找下一个机器人
        if row_robot_account.HOLD_STOCK_NUMBER >= p_max_hold_etf_number then
          continue;
        end if;
      
        -- 当前账户的资金资产、总资产和持股数量
        select t.capital_assets, t.total_assets, t.hold_stock_number
          into v_capital_assets, v_total_assets, v_hold_etf_number
          from robot6_account t
         where t.robot_name = row_robot_account.robot_name;
      
        for row_robot_etf_filter in cur_robot_etf_filter loop
          -- 查找这只etf的收盘价
          select t.close_price
            into v_close_price
            from etf_transaction_data t
           where t.code_ = row_robot_etf_filter.stock_code
             and t.date_ = to_date(p_buy_date, 'yyyy-mm-dd');
        
          -- 如果etf的收盘价过高，或者资金资产太少，连一手也买不了，则直接查找下一个机器人
          exit when v_close_price * 100 > v_capital_assets;
        
          -- 如果当前账户的持股数量大于等于最大持股数量，则直接查找下一个机器人
          exit when v_hold_etf_number >= p_max_hold_etf_number;
        
          -- 如果可以买，则向robot6_etf_transact_record表中插入数据，从robot6_etf_filter表中删除这条记录。
          -- 计算应该买入etf的数量
          /*select count(*)
            into v_robot6_etf_filter_number
            from robot6_etf_filter;
          if v_robot6_etf_filter_number <=
             (p_max_hold_etf_number - v_hold_etf_number) then
            v_should_buy_etf_number := v_robot6_etf_filter_number;
          else
            v_should_buy_etf_number := p_max_hold_etf_number -
                                       v_hold_etf_number;
          end if;*/
          v_should_buy_etf_number := p_max_hold_etf_number -
                                     v_hold_etf_number;
          -- 计算买多少股
          v_buy_or_sell_amount := 100;
          while (v_capital_assets / v_should_buy_etf_number) >=
                (v_buy_or_sell_amount * v_close_price) loop
            exit when(v_buy_or_sell_amount * v_close_price) >=(v_capital_assets /
                                                               v_should_buy_etf_number);
            /*while v_capital_assets >= (v_buy_or_sell_amount * v_close_price) loop
            exit when(v_buy_or_sell_amount * v_close_price) >=(v_capital_assets /
                                                               v_should_buy_etf_number);*/
            v_buy_or_sell_amount := v_buy_or_sell_amount + 100;
          end loop;
          if v_capital_assets < (v_buy_or_sell_amount * v_close_price) then
            v_buy_or_sell_amount := v_buy_or_sell_amount - 100;
          end if;
        
          -- 向robot6_stock_transact_record表中插入数据
          -- 做多
          if row_robot_etf_filter.direction = 1 then
            insert into robot6_stock_transact_record
              (robot_name,
               stock_code,
               buy_date,
               buy_price,
               buy_amount,
               filter_type,
               direction)
            values
              (row_robot_account.robot_name,
               row_robot_etf_filter.stock_code,
               to_date(p_buy_date, 'yyyy-mm-dd'),
               v_close_price,
               v_buy_or_sell_amount,
               row_robot_etf_filter.filter_type,
               row_robot_etf_filter.direction);
          end if;
          commit;
        
          -- 计算这支etf的持股数量、资金资产
          v_hold_etf_number := v_hold_etf_number + 1;
          v_capital_assets  := v_capital_assets -
                               (v_close_price * v_buy_or_sell_amount);
        
          -- 从robot6_stock_filter表中删除这条记录
          delete from robot6_stock_filter t
           where t.stock_code = row_robot_etf_filter.stock_code;
          commit;
        end loop;
      end loop;
    end;
  end buy_or_sell_avg_hold_space;

  --------------------------------------------------------------------------------------------------

  -- 尽量填满所有仓位
  -- 买股票，只向robot6_stock_transaction_record表中插入记录，只更新robot6_account表的hold_stock_number字段
  procedure buy_or_sell_max_hold_space(p_buy_date            in varchar2,
                                       p_max_hold_etf_number in number) as
  begin
    declare
      -- robot6_stock_filter表类型的变量
      row_robot_etf_filter robot6_stock_filter%rowtype;
      -- 收盘价
      v_close_price number;
      -- 买入多少股
      v_buy_or_sell_amount number;
      -- 当前的资金资产
      v_capital_assets number;
      -- 当前的总资产
      v_total_assets number;
      -- 当前持有etf的数量
      v_hold_etf_number number;
      -- 应当买入的etf的数量
      v_should_buy_etf_number number := 0;
      -- robot6_stock_filter表的记录数
      v_robot6_etf_filter_number number;
      -- 调用存储过程的返回值
      --v_macd_success_rate_array T_MACD_SUCCESS_RATE_ARRAY;
      -- 调用存储过程的返回值
      --v_c_p_ma5_success_rate_array T_C_P_MA5_SUCCESS_RATE_ARRAY;
      -- 调用存储过程的返回值
      --v_h_k_a_success_rate_arr T_H_K_A_SUCCESS_RATE_ARRAY;
      -- 当前的成功率（有延迟）
      -- v_current_success_rate number;
      -- 最大成功率
      --v_max_success_rate number;
      -- 最小成功率
      --v_min_success_rate number;
      -- 可以有的最大仓位
      --v_max_shipment_space number;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot6_account;
    begin
      for row_robot_account in cur_robot_account loop
        -- 如果当前账户的持股数量已经大于等于最大持股数量，则查找下一个机器人
        if row_robot_account.HOLD_STOCK_NUMBER >= p_max_hold_etf_number then
          continue;
        end if;
      
        -- 当前账户的资金资产、总资产和持股数量
        select t.capital_assets, t.total_assets, t.hold_stock_number
          into v_capital_assets, v_total_assets, v_hold_etf_number
          from robot6_account t
         where t.robot_name = row_robot_account.robot_name;
      
        while true loop
          -- 按照accumulative_profit_loss列降序排列，取第一条记录；如果robot6_stock_filter表中已经没有数据了，则查找下一个机器人
          begin
            select *
              into row_robot_etf_filter
              from (select *
                      from robot6_stock_filter t
                     order by t.accumulative_profit_loss desc)
             where rownum = 1;
            -- where rownum <= p_max_hold_etf_number;
          exception
            when no_data_found then
              -- 说明etf在这一天没有交易记录
              DBMS_OUTPUT.put_line('表【robot6_stock_filter】为空');
              exit;
          end;
        
          -- 查找这只etf的收盘价
          select t.close_price
            into v_close_price
            from etf_transaction_data t
           where t.code_ = row_robot_etf_filter.stock_code
             and t.date_ = to_date(p_buy_date, 'yyyy-mm-dd');
        
          -- 如果etf的收盘价过高，或者资金资产太少，连一手也买不了，则直接查找下一个机器人
          exit when v_close_price * 100 > v_capital_assets;
        
          -- 如果当前账户的持股数量大于等于最大持股数量，则直接查找下一个机器人
          exit when v_hold_etf_number >= p_max_hold_etf_number;
        
          -- 如果可以买，则向robot6_stock_transact_record表中插入数据，从robot6_stock_filter表中删除这条记录。
          -- 计算应该买入etf的数量
          select count(*)
            into v_robot6_etf_filter_number
            from robot6_stock_filter;
          if v_robot6_etf_filter_number <=
             (p_max_hold_etf_number - v_hold_etf_number) then
            v_should_buy_etf_number := v_robot6_etf_filter_number;
          else
            v_should_buy_etf_number := p_max_hold_etf_number -
                                       v_hold_etf_number;
          end if;
          /*v_should_buy_etf_number := p_max_hold_etf_number -
          v_hold_etf_number;*/
          -- 计算买多少股
          v_buy_or_sell_amount := 100;
          /*while (v_capital_assets / v_should_buy_etf_number) >=
              (v_buy_or_sell_amount * v_close_price) loop
          exit when(v_buy_or_sell_amount * v_close_price) >=(v_capital_assets /
                                                             v_should_buy_etf_number);*/
          while v_capital_assets >= (v_buy_or_sell_amount * v_close_price) loop
            exit when(v_buy_or_sell_amount * v_close_price) >=(v_capital_assets /
                                                               v_should_buy_etf_number);
            v_buy_or_sell_amount := v_buy_or_sell_amount + 100;
          end loop;
          if v_capital_assets < (v_buy_or_sell_amount * v_close_price) then
            v_buy_or_sell_amount := v_buy_or_sell_amount - 100;
          end if;
        
          -- 向robot6_stock_transact_record表中插入数据
          -- 做多
          if row_robot_etf_filter.direction = 1 then
            insert into robot6_stock_transact_record
              (robot_name,
               stock_code,
               buy_date,
               buy_price,
               buy_amount,
               filter_type,
               direction)
            values
              (row_robot_account.robot_name,
               row_robot_etf_filter.stock_code,
               to_date(p_buy_date, 'yyyy-mm-dd'),
               v_close_price,
               v_buy_or_sell_amount,
               row_robot_etf_filter.filter_type,
               row_robot_etf_filter.direction);
          end if;
          commit;
        
          -- 计算这支etf的持股数量、资金资产
          v_hold_etf_number := v_hold_etf_number + 1;
          v_capital_assets  := v_capital_assets -
                               (v_close_price * v_buy_or_sell_amount);
        
          -- 从robot6_stock_filter表中删除这条记录
          delete from robot6_stock_filter t
           where t.stock_code = row_robot_etf_filter.stock_code;
          commit;
        end loop;
      end loop;
    end;
  end buy_or_sell_max_hold_space;

  --------------------------------------------------------------------------------------------------

  -- 根据当日买入etf的收盘价，在买etf之后，更新robot_account表
  -- 存储过程名称：update_robot_account_after_buy_or_sell
  procedure update_robot_account_after_b_s(p_date in varchar2) as
  begin
    declare
      -- 机器人账号
      v_robot_name varchar2(20);
      -- etf资产
      v_etf_assets number;
      -- 买入etf的数量
      v_sell_or_buy_etf_number number;
      -- etf的收盘价
      v_current_close_price number;
      -- 返回机器人账户
      cursor cur_robot_account is
        select * from robot6_account;
      -- 计算每一个机器人账户，在某一天卖完etf后的收益
      cursor cur_bull_etf is
        select *
          from robot6_stock_transact_record rstr
         where rstr.robot_name = v_robot_name
           and rstr.direction = 1
           and rstr.sell_date is null
           and rstr.sell_price is null
           and rstr.sell_amount is null;
    begin
      ----------------------------------- 做多 ---------------------------------
      for row_robot_account in cur_robot_account loop
        -- 机器人账户名称
        v_robot_name             := row_robot_account.robot_name;
        v_etf_assets             := 0;
        v_sell_or_buy_etf_number := 0;
        v_current_close_price    := null;
      
        -- 做多
        for row_robot_etf_tranc_record in cur_bull_etf loop
        
          -- 查找某只etf在某一天的收盘价
          begin
            select t.close_price
              into v_current_close_price
              from etf_transaction_data t
             where t.code_ = row_robot_etf_tranc_record.stock_code
               and t.date_ = to_date(p_date, 'yyyy-mm-dd');
          exception
            when no_data_found then
              v_current_close_price := null;
              -- 说明etf在这一天没有交易记录
              DBMS_OUTPUT.put_line('etf【' ||
                                   row_robot_etf_tranc_record.stock_code ||
                                   '】在日期【' || p_date || '】没有交易记录');
          end;
          -- 如果在某一天没有收盘价，比如停牌，则查找最近一个交易日的收盘价
          if v_current_close_price is null then
            select std.close_price
              into v_current_close_price
              from (select *
                      from etf_transaction_data t
                     where t.code_ = row_robot_etf_tranc_record.stock_code
                       and t.date_ < to_date(p_date, 'yyyy-mm-dd')
                     order by t.date_ desc) std
             where rownum <= 1;
          end if;
        
          -- 卖完etf后的收益
          v_etf_assets             := v_etf_assets +
                                      v_current_close_price *
                                      row_robot_etf_tranc_record.buy_amount;
          v_sell_or_buy_etf_number := v_sell_or_buy_etf_number + 1;
        end loop;
      
        -- 更新robot6_account表
        update robot6_account t
           set t.hold_stock_number = v_sell_or_buy_etf_number,
               t.stock_assets      = v_etf_assets,
               t.capital_assets  = t.total_assets - v_etf_assets
         where t.robot_name = v_robot_name;
        commit;
      
        -- 向表robot6_account_log中插入数据
        select *
          into row_robot_account
          from robot6_account t
         where t.robot_name = row_robot_account.robot_name;
        insert into robot6_account_log
          (DATE_,
           ROBOT_NAME,
           HOLD_STOCK_NUMBER,
           STOCK_ASSETS,
           CAPITAL_ASSETS,
           TOTAL_ASSETS)
        values
          (to_date(p_date, 'yyyy-mm-dd'),
           row_robot_account.robot_name,
           row_robot_account.hold_stock_number,
           row_robot_account.stock_assets,
           row_robot_account.capital_assets,
           row_robot_account.total_assets);
        commit;
      end loop;
    end;
  
  end update_robot_account_after_b_s;
end PKG_ROBOT6;